package com.example.luke.cashorderingsystem.Util;

import android.Manifest;
import android.app.Activity;
import android.content.Context;
import android.content.pm.PackageManager;
import android.os.Environment;
import android.support.v4.app.ActivityCompat;


import com.example.luke.cashorderingsystem.Common.Setting;
import com.example.luke.cashorderingsystem.Model.Good;
import com.example.luke.cashorderingsystem.Model.GoodType;
import com.example.luke.cashorderingsystem.Model.Operator;
import com.example.luke.cashorderingsystem.Model.PurchaseReport;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

//import org.apache.poi.hssf.usermodel.HSSFCell;
//import org.apache.poi.hssf.usermodel.HSSFRow;
//import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//import org.apache.poi.openxml4j.opc.OPCPackage;
//import org.apache.poi.poifs.filesystem.POIFSFileSystem;
//import org.apache.poi.ss.usermodel.Cell;
//import org.apache.poi.ss.usermodel.CellValue;
//import org.apache.poi.ss.usermodel.Comment;
//import org.apache.poi.ss.usermodel.FormulaEvaluator;
//import org.apache.poi.ss.usermodel.Row;
//import org.apache.poi.ss.usermodel.Sheet;
//import org.apache.poi.ss.usermodel.Workbook;
//import org.apache.poi.ss.usermodel.WorkbookFactory;
//import org.apache.poi.xssf.usermodel.XSSFSheet;
//import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Created by luke on 23/05/2017.
 */

public class ExcelUtils {

   private static File file=new File(Environment.getExternalStorageDirectory().toString()+"/"+"现金消费");

public  static void getFileList(Context context){

    //初始化
//    Student.clearAllStudent(context);//先清空学生列表
//    Course.clearAllCourse(context);

    File file=new File(Environment.getExternalStorageDirectory().toString()+"/"+"现金消费");

    File fileList[]=file.listFiles();

    for (File temp:fileList){



        if (temp.getName().contains("课程表")){


            try {

                //initCourseClass(context,temp.getAbsolutePath());

            }catch (Exception e){

            }

        }

//        if (!temp.getName().contains("课程表")){
//
//
//            try {
//
//
//                initStudentList(context,temp.getAbsolutePath());
//
//            }catch (Exception e){
//
//            }
//
//        }
    }
}

    public static void syncFoodData(){

        initFoodType();

        initFoodList();

    }

    public  static void initFoodList(){

        //初始化
       // Student.clearAllStudent(context);//先清空学生列

        Good.removeAllGood(GlobalFunction.context);

        File file=new File(Environment.getExternalStorageDirectory().toString()+"/"+"现金消费");

        File fileList[]=file.listFiles();

        for (File temp:fileList){


            if (temp.getName().contains("商品列表")) {

              //  initStudentList(context,temp.getAbsolutePath());
                parseFoodList(temp.getAbsolutePath());
            }

        }
    }

    public  static void initFoodType(){

        //初始化
        // Student.clearAllStudent(context);//先清空学生列
        GoodType.removeAllGoodType();

         GoodType goodType=new GoodType("1","炒菜");
        GoodType.storeToDB(GlobalFunction.context,goodType);

        GoodType goodType2=new GoodType("2","精品菜");
        GoodType.storeToDB(GlobalFunction.context,goodType2);


        GoodType goodType3=new GoodType("2","蒸菜");
        GoodType.storeToDB(GlobalFunction.context,goodType3);


        GoodType goodType4=new GoodType("2","特色菜");
        GoodType.storeToDB(GlobalFunction.context,goodType4);


        GoodType goodType5=new GoodType("1","早餐");
        GoodType.storeToDB(GlobalFunction.context,goodType5);

        GoodType goodType6=new GoodType("2","汤类");
        GoodType.storeToDB(GlobalFunction.context,goodType6);


        GoodType goodType7=new GoodType("2","套餐");
        GoodType.storeToDB(GlobalFunction.context,goodType7);


        GoodType goodType8=new GoodType("2","乘务饭");
        GoodType.storeToDB(GlobalFunction.context,goodType8);

        GoodType goodType9=new GoodType("2","添乘人员用餐");
        GoodType.storeToDB(GlobalFunction.context,goodType9);


        GoodType goodType10=new GoodType("2","测试菜单");
        GoodType.storeToDB(GlobalFunction.context,goodType10);
    }


    private static void parseFoodList(String classFilePath) {

        jxl.Workbook readwb = null;


        try

        {

            //构建Workbook对象, 只读Workbook对象

            //直接从本地文件创建Workbook

            InputStream instream = new FileInputStream(classFilePath);

            readwb = Workbook.getWorkbook(instream);


            //Sheet的下标是从0开始

            //获取第一张Sheet表

            Sheet readsheet = readwb.getSheet(0);

            //获取Sheet表中所包含的总列数

            int rsColumns = readsheet.getColumns();

            //获取Sheet表中所包含的总行数

            int rsRows = readsheet.getRows();

            //获取指定单元格的对象引用

            for (int i = 1; i < rsRows; i++)

            {
             //   ClassBean course = new ClassBean();

                Good good=new Good();


                for (int j = 0; j < rsColumns; j++)

                {

                    Cell cell = readsheet.getCell(j, i);

                    String val = cell.getContents();

                GlobalFunction.printTextByTag(j+":"+val);

                    switch (j) {
                        case 0:

                           good.setId(val);

                        break;


                        case 1:
                            good.setName(val);
                            break;

                        case 2:
                            good.setBarcode(val);
                            break;

                        case 4:
                            good.setPinyinID(val);
                            break;

                        case 6:
                            good.setPriceIn(val);
                            break;

                        case 7:
                            good.setPriceOut(val);
                            break;

                        case 8:
                            good.setKind(val);
                            break;

                    }
                    good.setDisoountable("0");
                    good.setStoredNumber("100");


                }
                Good.storeToDB(GlobalFunction.context,good);

            }


        } catch (Exception e) {

            e.printStackTrace();
        }


    }

    private static void createReportTitle(WritableSheet sheet) throws WriteException{

        String [] header=new String[]{"机号","操作员","单号","商品编号","名称","价格","消费日期","数量","存库数量"};


        for (int i=0;i<header.length;i++){

          WritableFont font1= new WritableFont(WritableFont.TIMES, 13);

            WritableCellFormat format1=new WritableCellFormat(font1);
            format1.setAlignment(jxl.format.Alignment.CENTRE);


            Label label=new Label(i,0,header[i],format1);
            sheet.addCell(label);



            switch (i){
                case 0:
                    sheet.setColumnView(i,20);
                    break;

                case 4:
                    sheet.setColumnView(i,20);
                    break;

                default:
                    sheet.setColumnView(i,10);
                    break;


            }



        }
    }

   public static void exportExcel(){
        // 初始化工作表
        WritableWorkbook book = null;
        try {
            book = Workbook.createWorkbook(new File(file.getAbsolutePath()+"/消费报告.xls"));
            WritableSheet sheet = book.createSheet("报告", 0);


            createReportTitle(sheet);

            book.write();




        }catch (Exception e){

            e.printStackTrace();


        }finally {

            if(book!=null){
                try {
                    book.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }



    }

    public static void exportExcel(ArrayList<PurchaseReport>purchaseReportArrayList){
        // 初始化工作表
        WritableWorkbook book = null;
        String path=file.getAbsolutePath()+"/消费报告"+Setting.getInstance().getDevID()+"-"+GlobalFunction.getStringFromDate(new Date(),"yyMMdd-HHmmss")+".xls";
        try {
            book = Workbook.createWorkbook(new File(path));

            WritableSheet sheet = book.createSheet("报告", 0);



            createReportTitle(sheet);


            for (int i=1;i<=purchaseReportArrayList.size();i++){//列

                PurchaseReport report=purchaseReportArrayList.get(i-1);

                for (int j=0;j<9;j++){//行


                    String val="";

                    switch (j){
                        case 0:

                            val= Setting.getInstance().getDevID();

                            break;

                        case 1:
                            val= Operator.getIntance().getEmployeeID();
                            break;

                        case 2:

                            val=report.getTicketID();

                            break;

                        case 3:

                            val=report.getCommodityID();

                            break;

                        case 4:

                            val=report.getCommodityName();

                            break;

                        case 5:

                            val=report.getSaleCost();

                            break;

                        case 6:

                            val=report.getDateTime();

                            break;

                        case 7:
                            val=   String.valueOf(report.getVolume());

                            break;

                        case 8:

                            val=String.valueOf(report.getRestoredNum());

                            break;

                    }


                    WritableCellFormat format1=new WritableCellFormat();
                    format1.setAlignment(jxl.format.Alignment.CENTRE);

                    Label label=new Label(j,i,val,format1);
                    sheet.addCell(label);

                }


            }
            book.write();




        }catch (Exception e){

            e.printStackTrace();


        }finally {

            if(book!=null){
                try {
                    book.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

            GlobalFunction.ToastAlert(GlobalFunction.context,"导出成功");
        }



    }



//    public  static ArrayList<ClassBean> getClassList(Context context){
//
//
//        File file=new File(Environment.getExternalStorageDirectory().toString()+"/"+"省党校");
//
//        File fileList[]=file.listFiles();
//        ArrayList<ClassBean> classBeanArrayList=new ArrayList<>();
//
//        for (File temp:fileList){
//
//
//            if (temp.getName().contains("教室名称")) {
//
//                GlobalFunction.printTextByTag("123");
//           classBeanArrayList=  analyzeClassExcelFile   (temp.getAbsolutePath());
//
//            }
//
//        }
//
//        return classBeanArrayList;
//
//    }

//    private static ArrayList<ClassBean> analyzeClassExcelFile(String classFilePath){
//
//        jxl.Workbook readwb = null;
//        ArrayList<ClassBean> classBeanArrayList=new ArrayList<>();
//
//
//        try
//
//        {
//
//            //构建Workbook对象, 只读Workbook对象
//
//            //直接从本地文件创建Workbook
//
//            InputStream instream = new FileInputStream(classFilePath);
//
//            readwb = Workbook.getWorkbook(instream);
//
//
//            //Sheet的下标是从0开始
//
//            //获取第一张Sheet表
//
//            Sheet readsheet = readwb.getSheet(0);
//
//            //获取Sheet表中所包含的总列数
//
//            int rsColumns = readsheet.getColumns();
//
//            //获取Sheet表中所包含的总行数
//
//            int rsRows = readsheet.getRows();
//
//            //获取指定单元格的对象引用
//
//            for (int i = 1; i < rsRows; i++)
//
//            {
//                ClassBean course = new ClassBean();
//
//
//                for (int j = 0; j < rsColumns; j++)
//
//                {
//
//                    Cell cell = readsheet.getCell(j, i);
//
//                    String val = cell.getContents();
//
//                    switch (j) {
//                        case 1:
//                            course.className = val;
//
//                            break;
//                        case 2:
//                            course.classCode = val;
//
//                            break;
//
//
//                    }
//                }
//
//                classBeanArrayList.add(course);
//            }
//
//        }catch (Exception e){
//
//            e.printStackTrace();
//        }
//        return classBeanArrayList;
//
//   }

//  private  static  void initCourseClass(Context context, String classFilePath){
//
//      GlobalFunction.printTextByTag("init course");
//      jxl.Workbook readwb = null;
//      Course.getInstance(context).courseArrayList=new ArrayList<>();
//
//      try
//
//      {
//
//          //构建Workbook对象, 只读Workbook对象
//
//          //直接从本地文件创建Workbook
//
//          InputStream instream = new FileInputStream(classFilePath);
//
//          readwb = Workbook.getWorkbook(instream);
//
//
//          //Sheet的下标是从0开始
//
//          //获取第一张Sheet表
//
//          Sheet readsheet = readwb.getSheet(0);
//
//          //获取Sheet表中所包含的总列数
//
//          int rsColumns = readsheet.getColumns();
//
//          //获取Sheet表中所包含的总行数
//
//          int rsRows = readsheet.getRows();
//
//          //获取指定单元格的对象引用
//
//          for (int i = 1; i < rsRows; i++)
//
//          {
//              Course course=new Course();
//
//
//
//              for (int j = 0; j < rsColumns; j++)
//
//              {
//
//              Cell cell = readsheet.getCell(j, i);
//
//              String val=cell.getContents();
//
//             switch (j){
//                case 1:
//                    course.Time=val;
//
//                    break;
//                case 2:
//                    course.name1=val;
//
//                    break;
//                case 3:
//                    course.name2=val;
//
//                    break;
//                case 4:
//                    course.teacher=val;
//
//                    break;
//                case 5:
//                    course.className=val;
//
//                    break;
//
//            }
//              }
//
//
//     Course.getInstance(context).storeToDB(context,course);
//
//
//
//    }


//      }catch (Exception e){
//          GlobalFunction.printTextByTag(e.getMessage());
//      }
//
//}

//    private  static  void initStudentList(Context context, String classFilePath){
//        jxl.Workbook readwb = null;
//
//        try
//
//        {
//
//            //构建Workbook对象, 只读Workbook对象
//
//            //直接从本地文件创建Workbook
//
//            InputStream instream = new FileInputStream(classFilePath);
//
//            readwb = Workbook.getWorkbook(instream);
//
//
//            //Sheet的下标是从0开始
//
//            //获取第一张Sheet表
//
//            Sheet readsheet = readwb.getSheet(0);
//
//            //获取Sheet表中所包含的总列数
//
//            int rsColumns = readsheet.getColumns();
//
//            //获取Sheet表中所包含的总行数
//
//            int rsRows = readsheet.getRows();
//
//            //获取指定单元格的对象引用
//
//            for (int i = 1; i < rsRows; i++)
//
//            {
//
//                Student course=new Student();
//
//                for (int j = 0; j < rsColumns; j++)
//
//                {
//
//                    Cell cell = readsheet.getCell(j, i);
//                    String val = cell.getContents();
//
//
//                    switch (j) {
//                        case 1:
//                            course.className = val;
//
//                            break;
//                        case 2:
//                            course.name = val;
//
//                            break;
//                        case 3:
//                            course.employeeID = val;
//
//                            break;
//                        case 4:
//                            course.issueUnit = val;
//
//                            break;
//                        case 5:
//                            course.gender = val;
//
//                            break;
//                        case 6:
//                            course.age = val;
//
//                            break;
//                        case 7:
//                            course.culture = val;
//
//                            break;
//                        case 8:
//                            course.studentID = val;
//
//                            break;
//
//                    }
//
//                }
//        GlobalFunction.printTextByTag(course.className+""+course.name);
//                Student.saveStudent(context,course);
//           //     System.out.println();
//
//            }
//
//
//        }catch (Exception e){
//            GlobalFunction.printTextByTag(e.getMessage());
//        }
//
////        try {
////
////           Workbook book = WorkbookFactory.create(new File(classFilePath));
////
////            Sheet sheet=book.getSheetAt(0);
////            FormulaEvaluator formulaEvaluator=book.getCreationHelper().createFormulaEvaluator();
////
////            int rows=sheet.getPhysicalNumberOfRows();
////
////            for (int i=1;i<rows;i++){
////
////                Row row=sheet.getRow(i);
////
////
////                Student course= new Student();
////                int cellCount=row.getPhysicalNumberOfCells();
////
////                for (int c=0;c<cellCount;c++){
////                    String val=  getCellAsString(row,c,formulaEvaluator);
////
////                    switch (c){
////                        case 0:
////                            course.className=val;
////
////                            break;
////                        case 1:
////                            course.name=val;
////
////                            break;
////                        case 2:
////                            course.employeeID=val;
////
////                            break;
////                        case 3:
////                            course.issueUnit=val;
////
////                            break;
////                        case 4:
////                            course.gender=val;
////
////                            break;
////                        case 5:
////                            course.age=val;
////
////                            break;
////                        case 6:
////                            course.culture=val;
////
////                            break;
////                        case 7:
////                            course.studentID=val;
////
////                            break;
////
////                    }
////
////                }
////
////        Student.saveStudent(context,course);
////           //    GlobalFunction.printTextByTag(course.className+":"+course.name+":"+course.employeeID+":"+course.issueUnit+":"+course.studentID);
////
////
////            }
////
////
////        }catch(Exception e){
////
////            GlobalFunction.printTextByTag(e.getMessage());
////
////        }
//
//    }

    public static void jxReadEcel(String filePath) throws BiffException,IOException {
        jxl.Workbook readwb = null;

        try

        {

            //构建Workbook对象, 只读Workbook对象

            //直接从本地文件创建Workbook

            InputStream instream = new FileInputStream(filePath);

            readwb = Workbook.getWorkbook(instream);


            //Sheet的下标是从0开始

            //获取第一张Sheet表

            Sheet readsheet = readwb.getSheet(0);

            //获取Sheet表中所包含的总列数

            int rsColumns = readsheet.getColumns();

            //获取Sheet表中所包含的总行数

            int rsRows = readsheet.getRows();

            //获取指定单元格的对象引用

            for (int i = 0; i < rsRows; i++)

            {

                for (int j = 0; j < rsColumns; j++)

                {

                    Cell cell = readsheet.getCell(j, i);

                    GlobalFunction.printTextByTag(cell.getContents());
                    //  System.out.print(cell.getContents() + " ");

                }

                System.out.println();

            }


        }catch (Exception e){
            GlobalFunction.printTextByTag(e.getMessage());
        }
    }
    private   static  void readExcel(String filePath) {

//        try {
//
//
//        XSSFWorkbook book = new XSSFWorkbook(filePath);
//
//         XSSFSheet sheet=book.getSheetAt(0);
//         FormulaEvaluator formulaEvaluator=book.getCreationHelper().createFormulaEvaluator();
//
//        int rows=sheet.getPhysicalNumberOfRows();
//
//          for (int i=1;i<rows;i++){
//
//              Row row=sheet.getRow(i);
//
//              int cellCount=row.getPhysicalNumberOfCells();
//
//              for (int c=0;c<cellCount;c++){
//
//
//                 getCellAsString(row,c,formulaEvaluator);
//              }
//          }
//
//
//            }catch(Exception e){
//
//                GlobalFunction.printTextByTag(e.getMessage());
//
//            }

     }


//    private static String getCellAsString(Row row,int c,FormulaEvaluator formulaEvaluator){
//        String value="";
//
////        try {
////            Cell cell=row.getCell(c);
////
////            if (cell!=null){
////
////                CellValue cellValue=formulaEvaluator.evaluate(cell);
////
////                switch (cellValue.getCellType()){
////
////                    case Cell.CELL_TYPE_STRING:
////
////                        value=cellValue.getStringValue();
////
////                        break;
////
////                }
////
////            }
////
////
////
////
////        }catch (Exception e){
////
////            GlobalFunction.printTextByTag(e.getMessage());
////
////        }
//
//        return value;
//    }







    // Storage Permissions
    private static final int REQUEST_EXTERNAL_STORAGE = 1;
    private static String[] PERMISSIONS_STORAGE = {
            Manifest.permission.READ_EXTERNAL_STORAGE,
            Manifest.permission.WRITE_EXTERNAL_STORAGE,
            Manifest.permission.READ_PHONE_STATE, Manifest.permission.NFC
    };

    /**
     * Checks if the app has permission to write to device storage
     *
     * If the app does not has permission then the user will be prompted to grant permissions
     *
     * @param activity
     */
    public static boolean verifyStoragePermissions(Activity activity) {
        // Check if we have write permission
        int permission = ActivityCompat.checkSelfPermission(activity, Manifest.permission.WRITE_EXTERNAL_STORAGE);

        if (permission != PackageManager.PERMISSION_GRANTED) {
            // We don't have permission so prompt the user
            ActivityCompat.requestPermissions(
                    activity,
                    PERMISSIONS_STORAGE,
                    REQUEST_EXTERNAL_STORAGE
            );



            return false;

        }else
            return true;


    }

    public  static File creatDir(Activity context, String fileName){
        verifyStoragePermissions(context);



        File dir = new File(Environment.getExternalStorageDirectory().toString(),fileName);

        try{
            if (dir.exists()){

                return dir;

            }else {

                if(dir.mkdir()) {

                    //  GlobalFunction.ToastAlert(context,"创建文件夹成功");
                GlobalFunction.printTextByTag("Directory created");


                } else {

                    GlobalFunction.printTextByTag("Directory is not created");
                    //   GlobalFunction.ToastAlert(context,"创建文件夹失败");

                    return  null;


                }
            }

        }catch(Exception e){
            e.printStackTrace();
        }



        return  dir;


    }

}
